package com.web.daoimpl;
import com.web.dao.StudentDao;
import com.web.entity.Grade;
import com.web.entity.Stu_admin;
import com.web.entity.Student;
import com.web.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author 17612
 */
public class StudentDaoImpl extends BaseDaoImpl implements StudentDao {

    @Override
    //
    public int insertNewStudent(Student s) {
        return excuteUpdate_o("insert into Student values(?,?,?,?,?)",s.getSid(),s.getSname(),s.getSpwd(),s.getSgender(),s.getClassID());
    }
    
    @Override
    //
    public Student getOneBySid(String Sid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where Sid=?");
            ps.setString(1, Sid);
            rs = ps.executeQuery();
           while (rs.next()) {
                s = new Student(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
            } 
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return s;        
    }
    
/////////////////////////////////////管理员用于获取所有学生信息

    @Override
    public List<Stu_admin> getallStudent() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Stu_admin> allStudent = new ArrayList();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select Sid,Sname,Spwd,Sgender,Class.ClassID,ClassName from Student,Class where Student.ClassID=Class.ClassID");
            rset = ps.executeQuery();
            while (rset.next()) {
                allStudent.add(new Stu_admin(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getString(5),
                        rset.getString(6)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return allStudent;
    }///////////////////////////////////////////////////////////////////////////////////////////

    @Override
    public List<Stu_admin> getBykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Stu_admin> s = new ArrayList();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select Sid,Sname,Spwd,Sgender,Class.ClassID,ClassName from Student,Class  where  Student.ClassID=Class.ClassID and (Sid like ? or Sname like ? or ClassName like ?)");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            rset = ps.executeQuery();
            if (rset.next()) {
                s.add(new Stu_admin(rset.getString(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getString(6)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }

    @Override
    public int updateStudent(String sid, String sname, String spwd) {
        return excuteUpdate("update Student set Sname=?,Spwd=? where Sid=?", sname, spwd, sid);
    }

    @Override
    public int deleteStudent(String sid) {
        return excuteUpdate("delete from Grades where Sid=? delete from Student where Sid=? ", sid,sid);
    }

    @Override
    public List<Grade> getGrade() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Grade> grade = new ArrayList();
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select Grades.Sid,Sname,ClassName,CourseName,Grade from Grades,Student where Grades.Sid=Student.Sid order by Grade desc");
            rset = ps.executeQuery();
            while (rset.next()) {
                grade.add(new Grade(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getInt(5)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return grade;
    }

    @Override
    public List<Grade> getGbykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Grade> grade = new ArrayList();
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select Grades.Sid,Sname,ClassName,CourseName,Grade from Grades,Student where Grades.Sid=Student.Sid and (Sname like ? or ClassName like ? or Grades.Sid like ? or CourseName like ? or Grade like ?) order by Grade desc");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            ps.setString(4, "%" + keyword + "%");
            ps.setString(5, "%" + keyword + "%");
            rset = ps.executeQuery();
            while (rset.next()) {
                grade.add(new Grade(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getInt(5)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return grade;
    }

    @Override
    public int updateGrade(String sid, int grade, String coursename) {
        Connection con = null;
        PreparedStatement ps = null;
        int rows = 0;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("update Grades set Grade=? where Sid=? and CourseName=?");
            ps.setInt(1, grade);
            ps.setString(2, sid);
            ps.setString(3, coursename);
            rows = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(null, ps, con);
        }
        return rows;
    }

    @Override
    public List<Student> findStudentsByClassID(String ClassID) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where ClassID=?");
            ps.setString(1, ClassID);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return list;
    }

    @Override
    public Student findStudentBykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select Sid,Sname,Spwd,Sgender,Class.ClassID,ClassName from Student,Class  where  Student.ClassID=Class.ClassID and Sid like ? or Sname like ? or ClassName like ?");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            rs = ps.executeQuery();
            if (rs.next()) {
                s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }

    @Override
    public Stu_admin getoneBysid(String sid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        Stu_admin onestudent = new Stu_admin();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Student where Sid=?");
            ps.setString(1, sid);
            rset = ps.executeQuery();
            if (rset.next()) {
                onestudent.setSid(rset.getString(1));
                onestudent.setSname(rset.getString(2));
                onestudent.setSpwd(rset.getString(3));
                onestudent.setSgender(rset.getString(4));
                onestudent.setClassID(rset.getString(5));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return onestudent;
    }

    @Override
    //
    public int update(Student s) {
        return excuteUpdate("update Student set Sname=?,Sgender=?,ClassID=? where Sid=?",
                s.getSname(),
                s.getSgender(),
                s.getClassID(),
                s.getSid());
    }

    @Override
    //
    public int updateSpwd(Student s) {
        return excuteUpdate("update Student set Spwd=? where Sid=?",
                s.getSpwd(),
                s.getSid());
    }
//////////////////管理员判断学号
        @Override
    public boolean isRepeatStudent(int sid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select * from Student where Sid=?");
            ps.setInt(1, sid);
            rset=ps.executeQuery();
            if(rset.next()){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return false;
    }
}



    


    



